package com.senior.english.servicesImp;

import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;

import com.senior.english.config.Constant;
import com.senior.english.model.MediaModel;
import com.senior.english.model.QuestionaireModel;
import com.senior.english.model.UserModel;
import com.senior.english.utilities.ManageFile;
import com.senior.english.utilities.Util;

public class QuestionaireService {
	private JdbcTemplate jdbcTemplate;

	/**
	 * @return the jdbcTemplate
	 */
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	/**
	 * @param jdbcTemplate
	 *            the jdbcTemplate to set
	 */
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	LobHandler lobHandler = new DefaultLobHandler();

	/**
	 * List all questions
	 * 
	 * @return
	 */
	@SuppressWarnings({ "deprecation", "unchecked" })
	public List<QuestionaireModel> listQuestion() {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate())
				.withProcedureName("spd_listQuestionaire").returningResultSet(
						"questions",
						ParameterizedBeanPropertyRowMapper
								.newInstance(QuestionaireModel.class));
		Map<String, Object> m = sjc.execute(new HashMap<String, Object>(0));
		List<QuestionaireModel> listQuestion = (List<QuestionaireModel>) m
				.get("questions");
		return listQuestion;
	}

	public void createTest(String testName) {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate())
				.withProcedureName("spd_createTest");
		MapSqlParameterSource mp = new MapSqlParameterSource();
		mp.addValue("partId", 3);
		mp.addValue("testname", testName,Types.VARCHAR);
		mp.addValue("testyear", Util.getCurrentYear());
		sjc.execute(mp);

	}

	/**
	 * List all image questions
	 * 
	 * @return
	 */
	@SuppressWarnings({ "deprecation", "unchecked" })
	public List<QuestionaireModel> listImgQuestion() {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate())
				.withProcedureName("spd_randomQuestions").returningResultSet(
						"imgQuestions",
						ParameterizedBeanPropertyRowMapper
								.newInstance(QuestionaireModel.class));
		MapSqlParameterSource mp = new MapSqlParameterSource();
		mp.addValue("partId", 1);
		Map<String, Object> m = sjc.execute(mp);
		List<QuestionaireModel> listImgQuestion = (List<QuestionaireModel>) m
				.get("imgQuestions");
		return listImgQuestion;
	}

	/**
	 * insert image to database and server
	 * 
	 * @param path
	 * @param fileName
	 */
	public void insertImage(String path, String fileName) {
		String fullPath = path + fileName;
		final File f1 = new File(fullPath);
		FileInputStream fin = null;
		try {
			fin = new FileInputStream(f1);
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		final FileInputStream fins = fin;
		jdbcTemplate.execute("call spd_insertImage(?)",
				new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
					protected void setValues(PreparedStatement ps,
							LobCreator lobCreator) throws SQLException {
						lobCreator.setBlobAsBinaryStream(ps, 1, fins,
								(int) f1.length());
					}
				});

		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate())
				.withProcedureName("spd_getLastMediaId");
		Map<String, Object> result = sjc.execute();
		String mediaId = result.get("mediaid").toString();
		ManageFile mfile = new ManageFile();
		mfile.renameFile(path, fileName, mediaId, ".png");
	}

	/**
	 * Load image from database
	 * 
	 * @throws SQLException
	 * @throws IOException
	 */
	@SuppressWarnings({ "deprecation", "unchecked" })
	public void loadImage() throws SQLException, IOException {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate())
				.withProcedureName("spd_loadImage").returningResultSet(
						"imgload",
						ParameterizedBeanPropertyRowMapper
								.newInstance(MediaModel.class));
		MapSqlParameterSource mp = new MapSqlParameterSource();
		mp.addValue("id", 12);
		Map<String, Object> m = sjc.execute(mp);
		final List<MediaModel> question_img = (List<MediaModel>) m
				.get("imgload");

		InputStream in = new ByteArrayInputStream(question_img.get(0)
				.getQuestion_img());
		BufferedImage bImageFromConvert = ImageIO.read(in);

		ImageIO.write(bImageFromConvert, "jpg", new File(Constant.serverImgPath
				+ "test.jpg"));
	}

	/**
	 * insert audio into database and server
	 * 
	 * @param path
	 * @param fileName
	 */
	public void insertAudio(String path, String fileName) {
		String fullPath = path + fileName;

		final File f1 = new File(fullPath);
		FileInputStream fin = null;
		try {
			fin = new FileInputStream(f1);
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		final FileInputStream fins = fin;
		jdbcTemplate.execute("call spd_insertAudio(?)",
				new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
					protected void setValues(PreparedStatement ps,
							LobCreator lobCreator) throws SQLException {
						lobCreator.setBlobAsBinaryStream(ps, 1, fins,
								(int) f1.length());
					}
				});
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate())
				.withProcedureName("spd_getLastMediaId");
		Map<String, Object> result = sjc.execute();
		String mediaId = result.get("mediaid").toString();
		ManageFile mfile = new ManageFile();
		mfile.renameFile(path, fileName, mediaId, ".mp3");
	}

	@SuppressWarnings({ "deprecation", "unchecked" })
	public void loadAudio() throws SQLException, IOException {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate())
				.withProcedureName("spd_loadAudio").returningResultSet(
						"audioload",
						ParameterizedBeanPropertyRowMapper
								.newInstance(MediaModel.class));
		MapSqlParameterSource mp = new MapSqlParameterSource();
		mp.addValue("id", 12);
		Map<String, Object> m = sjc.execute(mp);
		final List<MediaModel> question_audio = (List<MediaModel>) m
				.get("audioload");
		FileOutputStream fileOuputStream = new FileOutputStream(
				Constant.serverAudPath + "test.mp3");
		fileOuputStream.write(question_audio.get(0).getQuestion_audio());
		fileOuputStream.close();
	}

}
